using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using System.Collections;
using System.Web;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;

/// <summary>
/// The main Data Access Layer class which provides generic static methods for other
/// DAL classes to use
/// </summary>
public class DAL
{

    /// <summary>
    /// Get the connection string
    /// </summary>
    public static string ConnectionString
    {
        get
        {
            ConnectionStringSettings setting = ConfigurationManager.ConnectionStrings["TarsConnection"];
            if (setting == null)
            {
                string errorString = "You must configure the connection string in the web config file.";
                HttpContext.Current.Response.Redirect("~/Errors/ConnectionError.aspx");
            }
            return setting.ConnectionString;
        }
    }

    /// <summary>
    /// Create a new connection then return a new SqlCommand using that connection
    /// </summary>
    /// <returns>A new SqlCommand with a new connection</returns>
    public static SqlCommand CreateCommand()
    {
        SqlConnection conn = new SqlConnection(DAL.ConnectionString);
        return conn.CreateCommand();
    }

  
    /// <summary>
    /// Open connection and execute the SqlCommand, return a SqlDataReader
    /// </summary>
    /// <param name="command">SqlCommand</param>
    /// <returns>A SqlDataReader which can traverse through the result of the command</returns>
    public static SqlDataReader ExecuteReader(SqlCommand command)
    {
        SqlDataReader reader = null;
        try
        {
            if (command.Connection.State == ConnectionState.Closed)
                command.Connection.Open();
            reader = command.ExecuteReader();
        }
        catch 
        {
            throw;
            //HttpContext.Current.Response.Redirect("~/Errors/ConnectionError.aspx");
        }
        return reader;
    }

  
    /// <summary>
    /// Opens connection and executes the SqlCommand, returns the number of rows affected
    /// </summary>
    /// <param name="command">SqlCommand</param>
    /// <returns>an integer represents the number of rows affected</returns>
    public static int ExecuteNonQuery(SqlCommand command)
    {
        int n = 0;
        try
        {
            if (command.Connection.State == ConnectionState.Closed)
                command.Connection.Open();
            n = command.ExecuteNonQuery();
        }
        catch
        {
            throw;
            //HttpContext.Current.Response.Redirect("~/Errors/ConnectionError.aspx");
        }
        return n;
    }
   

    /// <summary>
    /// Generic method, execute the command text with parameters
    /// </summary>
    /// <typeparam name="T">T is a type which implements IBusinessEntity interface</typeparam>
    /// <param name="commandText">the query string</param>
    /// <param name="parameterNames">array of parameter names</param>
    /// <param name="parameterValues">array of parameter values</param>
    /// <returns>a list of objects with type T</returns>
    public static List<T> GetDataItems<T>(string commandText, string[] parameterNames, object[] parameterValues)
        where T : IBusinessEntity, new()
    {
        SqlCommand cmd = DAL.CreateCommand();
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = commandText;

        if (parameterNames != null && parameterNames.Length > 0)
        {
            for (int i = 0; i < parameterNames.Length; i++)
                cmd.Parameters.AddWithValue(parameterNames[i], parameterValues[i]);
        }

        List<T> dataItems = new List<T>();
        try
        {
            using (SqlDataReader reader = DAL.ExecuteReader(cmd))
            {
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        T dataItem = new T();
                        dataItem.Fill(reader);
                        dataItems.Add(dataItem);
                    }

                }
            }
        }
        catch 
        {
            throw;
            //HttpContext.Current.Response.Redirect("~/Errors/ConnectionError.aspx");
        }

        return dataItems;
    }

    /// <summary>
    /// Generic method, execute the command text with parameters
    /// </summary>
    /// <typeparam name="T">T is a type which implements IBusinessEntity interface</typeparam>
    /// <param name="commandText">the query string</param>
    /// <param name="parameterNames">array of parameter names</param>
    /// <param name="parameterValues">array of parameter values</param>
    /// <returns>an object of type T</returns>
    public static T GetDataItem<T>(string commandText, string[] parameterNames, object[] parameterValues)
            where T : IBusinessEntity, new()
    {
        SqlCommand cmd = DAL.CreateCommand();
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = commandText;

        if (parameterNames != null && parameterNames.Length > 0)
        {
            for (int i = 0; i < parameterNames.Length; i++)
            {
                cmd.Parameters.AddWithValue(parameterNames[i], parameterValues[i]);
            }
        }

        T dataItem = new T();
        try
        {
            using (SqlDataReader reader = ExecuteReader(cmd))
            {
                if (reader != null && reader.Read())
                    dataItem.Fill(reader);
            }
        }
        catch
        {
            throw;
            //HttpContext.Current.Response.Redirect("~/Errors/ConnectionError.aspx");
        }
        return dataItem;
    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="reader"></param>
    /// <param name="columnName"></param>
    /// <returns></returns>
    public static bool ColumnExists(SqlDataReader reader, string columnName)
    {
        DataView defaultView = reader.GetSchemaTable().DefaultView;
        defaultView.RowFilter = "ColumnName= '" + columnName + "'";
        return (defaultView.Count > 0);
    }
    public static void AddFilter(
                       ref string filterString,
                       string databaseFieldName,
                       string fieldInput,
                       string valueQuoteChar, string sourceTableOrView)
    {
        string fieldValue = fieldInput.Trim();

        // If input field left blank, don't filter on it.
        if (fieldValue == "") return;

        // Escape for quotes if we're going to quote the value
        string escapedFieldValue = fieldValue;
        if (valueQuoteChar == @"'") escapedFieldValue = fieldValue.Replace(@"'", @"''");

        // Add boolean clause to the complete filter expression
        // This method always uses operator =
        string booleanSqlExpression = "";
        if (databaseFieldName == "CreatedDate")
        {
            booleanSqlExpression =
                "{" + sourceTableOrView + "." + databaseFieldName + "} >= CDATE(" +
                valueQuoteChar +
                escapedFieldValue +
                valueQuoteChar+")";

            // separate boolean clauses with AND operator
            if (!string.IsNullOrEmpty(filterString)) filterString += " AND ";
        }
        else
        {
            booleanSqlExpression =
                "{" + sourceTableOrView + "." + databaseFieldName + "} = " +
                valueQuoteChar +
                escapedFieldValue +
                valueQuoteChar;

            // separate boolean clauses with AND operator
            if (!string.IsNullOrEmpty(filterString)) filterString += " AND ";

        }
        filterString += booleanSqlExpression;
    }
    public static ReportDocument CreateCrystalReportDocument(
                             string filterString,
                             string groupFieldName, string path, string sourceTableOrView)
    {
        ReportDocument rpt = new ReportDocument();

        // ---- Load the report definition
        string reportPath = path;
        rpt.Load(reportPath);

        // ---- Assign connection information for each table in the database

        // Build connection info
        ConnectionInfo connectionInfo = new ConnectionInfo();
        connectionInfo.ServerName = ConfigurationManager.AppSettings["Server"];
        connectionInfo.DatabaseName = ConfigurationManager.AppSettings["Database"];
        connectionInfo.UserID = ConfigurationManager.AppSettings["uid"];
       connectionInfo.Password=ConfigurationManager.AppSettings["pwd"];

        // Assign to all tables used by the report
        Tables tables = rpt.Database.Tables;
        foreach (CrystalDecisions.CrystalReports.Engine.Table table in tables)
        {
            TableLogOnInfo tableLogonInfo = table.LogOnInfo;
            tableLogonInfo.ConnectionInfo = connectionInfo;
            table.ApplyLogOnInfo(tableLogonInfo);
        }

        // ---- Set the record selection. If filterString is null, all records will be selected.
        rpt.DataDefinition.RecordSelectionFormula = filterString;

        // ---- Set grouping field

        // We'll set the first level of grouping (group 0)
      //  Group group = rpt.DataDefinition.Groups[0];

        // We'll be grouping on a field in vSales ("vSales" is the value of sourceTableOrView)
        CrystalDecisions.CrystalReports.Engine.Table groupFieldTable = rpt.Database.Tables[sourceTableOrView];

        // Assign the field whose name is passed into this function via parameter groupFieldName
        //group.ConditionField = groupFieldTable.Fields[groupFieldName];

        return rpt;
    }
}

